/******************************************************************************
 * Product: Adempiere ERP & CRM Smart Business Solution                       *
 * Copyright (C) 1999-2006 ComPiere, Inc. All Rights Reserved.                *
 * This program is free software; you can redistribute it and/or modify it    *
 * under the terms version 2 of the GNU General Public License as published   *
 * by the Free Software Foundation. This program is distributed in the hope   *
 * that it will be useful, but WITHOUT ANY WARRANTY; without even the implied *
 * warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.           *
 * See the GNU General Public License for more details.                       *
 * You should have received a copy of the GNU General Public License along    *
 * with this program; if not, write to the Free Software Foundation, Inc.,    *
 * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.                     *
 * For the text or an alternative of this public license, you may reach us    *
 * ComPiere, Inc., 2620 Augustine Dr. #245, Santa Clara, CA 95054, USA        *
 * or via info@compiere.org or http://www.compiere.org/license.html           *
 *****************************************************************************/
package org.compiere.report;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.logging.Level;

import org.compiere.print.MPrintFormat;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Ini;
import java.sql.Timestamp;

/**
 *  Statement of Account
 *
 *  @author Jorg Janke
 *  @version $Id: FinStatement.java,v 1.2 2006/07/30 00:51:05 jjanke Exp $
 *  @author Low Heng Sin
 *  - Remove update balance option to resolved Feature Request [ 1557707 ] and
 *    bug [1619917]
 *
 *  @author victor.perez@e-evolution.com, e-Evolution http://www.e-evolution.com
 * 			<li> FR [ 2520591 ] Support multiples calendar for Org 
 *			@see http://sourceforge.net/tracker2/?func=detail&atid=879335&aid=2520591&group_id=176962
 *	@author Armen Rizal, Goodwill Consulting
 *			<li>FR [2857076] User Element 1 and 2 completion - https://sourceforge.net/tracker/?func=detail&aid=2857076&group_id=176962&atid=879335
 *   
 */
public class InsStockReport extends SvrProcess
{
	private Date			p_DateAcct_From = null;
	private Date			p_DateAcct_To = null;
	/**	Product Parameter				*/
	private int					p_M_Product_ID = 0;
	/**	Org Parameter					*/
	private int					p_AD_Org_ID = 0;	
	/**	Parameter Where Clause			*/
	private StringBuffer		m_parameterWhere1 = new StringBuffer();
	private StringBuffer		m_parameterWhere2 = new StringBuffer();
	/**	Start Time						*/
	private long 				m_start = System.currentTimeMillis();
	private String myDate1;
	private String myDate2;

	private static String		s_insert = "INSERT INTO T_STOCKREPORT "
		+ "(  AD_PINSTANCE_ID, M_WAREHOUSE_ID, M_PRODUCT_ID, M_ATTRIBUTESETINSTANCE_ID, AD_CLIENT_ID, AD_ORG_ID, " +
				"FROMDATE, M_PRODUCT_CATEGORY_ID, M_LOCATOR_ID, TODATE, OPQTY, OPAMT, OPRATE, INQTY, INAMT, INRATE," +
				"OUTQTY, OUTAMT, OUTRATE, CLQTY, CLAMT, CLRATE, C_UOM_ID) ";

	private static String		s_inlineSt = " ad.m_warehouse_id, ad.m_product_id, ad.M_ATTRIBUTESETINSTANCE_ID, " +
			"ad.ad_client_id, ad.ad_org_id, ad.FROMDATE, ad.m_product_category_id, ad.m_locator_id, ad.TODATE, " +
			"sum(ad.OPQTY) OPQTY, sum(ad.OPAMT) OPAMT, avg(ad.OPRATE) OPRATE, sum(ad.INQTY) INQTY, sum(ad.INAMT) INAMT, " +
			"avg(ad.INRATE) INRATE, sum(ad.OUTQTY) OUTQTY, sum(ad.OUTAMT) OUTAMT, avg(ad.OUTRATE) OUTRATE, " +
			"sum(ad.CLQTY) CLQTY, sum(ad.CLAMT) CLAMT, avg(ad.CLRATE) CLRATE, ad.C_UOM_ID from ( ";

	private static String		s_opn = " l.m_warehouse_id, fa.m_product_id, null M_ATTRIBUTESETINSTANCE_ID, " +
			"fa.ad_client_id, fa.ad_org_id, null FROMDATE, p.m_product_category_id, fa.m_locator_id, null TODATE, " +
			"fa.qty OPQTY, case when fa.amtacctdr = 0 then fa.amtacctcr else fa.amtacctdr end OPAMT, " +
			"((case when fa.amtacctdr = 0 then fa.amtacctcr else fa.amtacctdr end) / fa.qty) OPRATE, " +
			"0 INQTY, 0 INAMT, 0 INRATE, 0 OUTQTY, 0 OUTAMT, 0 OUTRATE, 0 CLQTY, 0 CLAMT, 0 CLRATE, p.c_uom_id " +
			"from fact_acct fa, M_Product_Acct pa, c_validcombination vc, m_product p, m_locator l " +
			"where fa.m_product_id = pa.m_product_id and fa.account_id = vc.account_id " +
			"and vc.c_validcombination_id = pa.p_asset_acct and p.m_product_id = fa.m_product_id and l.m_locator_id = fa.m_locator_id " +
			" ";
//	" and fa.m_product_id = 1000021	 and fa.datetrx < '17-Jan-11'";

	private static String		s_in = " l.m_warehouse_id, fa.m_product_id, null M_ATTRIBUTESETINSTANCE_ID, " +
			"fa.ad_client_id, fa.ad_org_id, null FROMDATE, p.m_product_category_id, fa.m_locator_id, null TODATE, " +
			"0 OPQTY, 0 OPAMT, 0 OPRATE, fa.qty INQTY, " +
			"case when fa.amtacctdr = 0 then fa.amtacctcr else fa.amtacctdr end INAMT, " +
			"((case when fa.amtacctdr = 0 then fa.amtacctcr else fa.amtacctdr end) / fa.qty) INRATE, " +
			"0 OUTQTY, 0 OUTAMT, 0 OUTRATE, 0 CLQTY, 0 CLAMT, 0 CLRATE, p.c_uom_id " +
			"from fact_acct fa, M_Product_Acct pa, c_validcombination vc, m_product p, m_locator l " +
			"where fa.m_product_id = pa.m_product_id and fa.account_id = vc.account_id " +
			"and vc.c_validcombination_id = pa.p_asset_acct and p.m_product_id = fa.m_product_id and l.m_locator_id = fa.m_locator_id " +
			" ";
//	" and fa.qty >0 and fa.m_product_id = 1000021	 and fa.datetrx between '17-Jan-11' and '28-Feb-11'";

	private static String		s_out = " l.m_warehouse_id, fa.m_product_id, null M_ATTRIBUTESETINSTANCE_ID, " +
			"fa.ad_client_id, fa.ad_org_id, null FROMDATE, p.m_product_category_id, fa.m_locator_id, null TODATE, " +
			"0 OPQTY, 0 OPAMT, 0 OPRATE, 0 INQTY, 0 INAMT, 0 INRATE, " +
			"fa.qty*-1 OUTQTY, case when fa.amtacctdr = 0 then fa.amtacctcr else fa.amtacctdr end OUTAMT, " +
			"((case when fa.amtacctdr = 0 then fa.amtacctcr else fa.amtacctdr end) / (fa.qty*-1)) OUTRATE, " +
			"0 CLQTY, 0 CLAMT, 0 CLRATE, p.c_uom_id " +
			"from fact_acct fa, M_Product_Acct pa, c_validcombination vc, m_product p, m_locator l " +
			"where fa.m_product_id = pa.m_product_id and fa.account_id = vc.account_id " +
			"and vc.c_validcombination_id = pa.p_asset_acct and p.m_product_id = fa.m_product_id and l.m_locator_id = fa.m_locator_id " +
			" ";
//	" and fa.qty < 0 and fa.m_product_id = 1000021	 and fa.datetrx between '17-Jan-11' and '28-Feb-11'";

	private static String		s_inlineEn = " ) ad group by ad.m_warehouse_id, ad.m_product_id, ad.M_ATTRIBUTESETINSTANCE_ID," +
			"ad.ad_client_id, ad.ad_org_id, ad.FROMDATE, ad.m_product_category_id, ad.m_locator_id, " +
			"ad.TODATE, ad.C_UOM_ID)";
	/**
	 *  Prepare - e.g., get Parameters.
	 */
	protected void prepare()
	{
		StringBuffer sb = new StringBuffer ("Record_ID=")
			.append(getRecord_ID());
		//	Parameter
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)
		{
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("FROMDATE"))
			{
				p_DateAcct_From = (Date)para[i].getParameter();
				p_DateAcct_To = (Date)para[i].getParameter_To();				
			}
			else if (name.equals("TODATE"))
			{
				p_DateAcct_To = (Date)para[i].getParameter_To();
			}
			else if (name.equals("M_Product_ID"))
				p_M_Product_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("AD_Org_ID"))
				p_AD_Org_ID = ((BigDecimal)para[i].getParameter()).intValue();			
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
		//	Mandatory DATEVALUE, M_Product_ID

		 myDate1 = new SimpleDateFormat("dd-MMM-yyyy").format(p_DateAcct_From);
		 myDate2 = new SimpleDateFormat("dd-MMM-yyyy").format(p_DateAcct_To);

		 if (p_M_Product_ID == 0) {			 
			 m_parameterWhere1.append(" and fa.datetrx < to_date('").append(myDate1).append("') and fa.AD_ORG_ID = ").append(p_AD_Org_ID)
				.append(" and fa.AD_CLIENT_ID = ").append(getAD_Client_ID());
			
			 m_parameterWhere2.append(" and fa.datetrx between to_date('").append(myDate1).append("') and to_date('").append(myDate2).append("') and fa.AD_ORG_ID = ")
			.append(p_AD_Org_ID).append(" and fa.AD_CLIENT_ID = ").append(getAD_Client_ID());			
		} else {
			 m_parameterWhere1.append(" and fa.M_Product_ID = ").append( p_M_Product_ID)
				.append(" and fa.datetrx < to_date('").append(myDate1).append("') and fa.AD_ORG_ID = ").append(p_AD_Org_ID)
				.append(" and fa.AD_CLIENT_ID = ").append(getAD_Client_ID());

			m_parameterWhere2.append(" and fa.M_Product_ID = ").append( p_M_Product_ID)
			.append(" and fa.datetrx between to_date('").append(myDate1).append("') and to_date('").append(myDate2).append("') and fa.AD_ORG_ID = ")
			.append(p_AD_Org_ID).append(" and fa.AD_CLIENT_ID = ").append(getAD_Client_ID());
		}


		log.fine(sb.toString());
	}	//	prepare

	/**************************************************************************
	 *  Perform process.
	 *  @return Message to be translated
	 */
	protected String doIt()
	{

		//  Delete (just to be sure)
//		StringBuffer sql = new StringBuffer ("DELETE T_STOCKREPORT");
//		int no = DB.executeUpdateEx(sql.toString(), get_TrxName());
		
		createLines();

		log.fine((System.currentTimeMillis() - m_start) + " ms");
		return "";
	}	//	doIt

	/**
	 * 	Create Beginning Balance Line
	 */
	private void createLines()
	{
		
		StringBuffer sb = new StringBuffer (s_insert)
		.append(" (SELECT ").append(getAD_PInstance_ID()).append(", ").append(s_inlineSt);
		sb.append(" SELECT ").append(getAD_PInstance_ID()).append(", ").append(s_opn).append(m_parameterWhere1).append(" union all ")
		.append(" SELECT ").append(getAD_PInstance_ID()).append(", ").append(s_in).append(m_parameterWhere2).append(" and fa.qty > 0 union all ")
		.append(" SELECT ").append(getAD_PInstance_ID()).append(", ").append(s_out).append(m_parameterWhere2).append(" and fa.qty < 0 ").append(s_inlineEn);
	
		//
		int no = DB.executeUpdate(sb.toString(), get_TrxName());
		log.fine("#" + no);
		log.finest(sb.toString());
		
		//update closing stock qty, amount and constants
		sb = new StringBuffer ("UPDATE T_STOCKREPORT tsr SET " +
				"tsr.FROMDATE = to_date('").append(myDate1)
				.append("'), tsr.TODATE = to_date('").append(myDate2)
				.append("'), tsr.CLQTY = tsr.OPQTY + tsr.INQTY - tsr.OUTQTY, " +
				"tsr.CLAMT = tsr.OPAMT + tsr.INAMT - tsr.OUTAMT")
		.append(" WHERE tsr.AD_PInstance_ID=").append(getAD_PInstance_ID());
		  
		no = DB.executeUpdate(sb.toString(), get_TrxName());
		log.fine("Name #" + no);
		log.finest("Name - " + sb);

		//update avg rates
		sb = new StringBuffer ("UPDATE T_STOCKREPORT tsr SET " +
				"tsr.OPRATE = tsr.OPAMT/case when tsr.OPQTY = 0 then 1 else tsr.OPQTY end, " +
				"tsr.INRATE = tsr.INAMT/case when tsr.INQTY = 0 then 1 else tsr.INQTY end, " +
				"tsr.OUTRATE = tsr.OUTAMT/case when tsr.OUTQTY = 0 then 1 else tsr.OUTQTY end, " +
				"tsr.CLRATE = tsr.CLAMT/case when tsr.CLQTY = 0 then 1 else tsr.CLQTY end")
		.append(" WHERE tsr.AD_PInstance_ID=").append(getAD_PInstance_ID());
		  
		no = DB.executeUpdate(sb.toString(), get_TrxName());
		log.fine("Name #" + no);
		log.finest("Name - " + sb);

		
	}	//	createDetailLines

}	//	FinStatement
